import pandas as pd
df1_new=pd.read_excel('SalesData处理版.xlsx')
df1_new.loc[:,'日期']=pd.to_datetime(df1_new.loc[:,'日期'].astype(str),format='%Y-%m-%d')
#把年和季度隔开
df1_new['year']=df1_new['日期'].dt.year
df1_new['quarter']=df1_new['日期'].dt.quarter
df1_new_survice2=df1_new.groupby(by=['国家','服务分类','year','quarter',])['销售额','利润'].sum().reset_index()
data_Commercial=df1_new_survice2[df1_new_survice2['服务分类']=='Commercial']
data_Public=df1_new_survice2[df1_new_survice2['服务分类']=='Public']
data_Residential=df1_new_survice2[df1_new_survice2['服务分类']=='Residential']
print(data_Commercial)
#Commercial的销售额月环比增长率
country1=data_Commercial['国家'].unique()
for i in country1:
  df1_diff1=data_Commercial.loc[data_Commercial['国家']==i,'销售额'].diff(periods=1)
  data_Commercial.loc[data_Commercial['国家']==i,'销售额月环比增长率']=df1_diff1/(data_Commercial.loc[data_Commercial['国家']==i,'销售额']-df1_diff1)

#Public的销售额月环比增长率
country2=data_Public['国家'].unique()
for i in country2:
  df1_diff2=data_Public.loc[data_Public['国家']==i,'销售额'].diff(periods=1)
  data_Public.loc[data_Public['国家']==i,'销售额月环比增长率']=df1_diff2/(data_Public.loc[data_Public['国家']==i,'销售额']-df1_diff2)

#Residential的销售额月环比增长率
country3=data_Residential['国家'].unique()
for i in country3:
  df1_diff3=data_Residential.loc[data_Residential['国家']==i,'销售额'].diff(periods=1)
  data_Residential.loc[data_Residential['国家']==i,'销售额月环比增长率']=df1_diff3/(data_Residential.loc[data_Residential['国家']==i,'销售额']-df1_diff3)

# Commercial的利润月环比增长率
country1 = data_Commercial['国家'].unique()
for i in country1:
  df1_diff11=data_Commercial.loc[data_Commercial['国家']==i,'利润'].diff(periods=1)
  data_Commercial.loc[data_Commercial['国家']==i,'利润月环比增长率']=df1_diff11/(data_Commercial.loc[data_Commercial['国家']==i,'利润']-df1_diff11)
data_Commercial.to_excel('Commercial的销售额利润年月环比增长率.xlsx')

#Public的利润月环比增长率
country2=data_Public['国家'].unique()
for i in country2:
  df1_diff22=data_Public.loc[data_Public['国家']==i,'利润'].diff(periods=1)
  data_Public.loc[data_Public['国家']==i,'利润月环比增长率']=df1_diff22/(data_Public.loc[data_Public['国家']==i,'利润']-df1_diff22)
data_Public.to_excel('Public的销售额利润月环比增长率.xlsx')

#Residential的利润月环比增长率
for i in country3:
    df1_diff33 = data_Residential.loc[data_Residential['国家'] == i, '利润'].diff(periods=1)
    data_Residential.loc[data_Residential['国家'] == i, '利润月环比增长率'] = df1_diff33/ (data_Residential.loc[data_Residential['国家'] == i, '利润'] - df1_diff33)
print(data_Residential)
data_Residential.to_excel('Residential的销售额利润月环比增长率.xlsx')